
--EXEC SP_SAVE_SALES_ORDER_LINE_ITEM_SHIP_INFO '300546', '9620', 1,1,2,'04/04/2009','SHIPPED','US1-Fremont', 'JUNGO LTD.', 'SRF # SH041009-2 Himalya II Boards', 'IKRS6836H-VX02-WL01', '0.0', '06/12/2009', '06/12/2009', '06/12/2009', '06/12/2009' 

ALTER PROC SP_SAVE_SALES_ORDER_LINE_ITEM_SHIP_INFO 
( 
	@ORDER_NO			VARCHAR(50),				
	@LABEL_NUMBER		VARCHAR(50),
	@LINE_NO			INT,
	@SHIPMENT_NO		INT,
	@LINE_ITEM_SHIP_QTY	INT,
	@SHIPPED_DATE		DATETIME,
	@LINEITEM_STATUS	VARCHAR(20),
	@SHIP_FROM_LOCATION VARCHAR(50),
	@CUSTOMER_NAME VARCHAR(50),
	@CUSTOMER_PO VARCHAR(50),
	@PART_NUMBER VARCHAR(50),
	@UNIT_PRICE FLOAT,
	@BOOKING_DATE VARCHAR(50),
	@SCH_DATE VARCHAR(50),
	@REV_SCH_DATE VARCHAR(50),
	@REQUESTED_DATE VARCHAR(50),
	@IS_SAMPLE INT,
	@SHIP_REQ_NO VARCHAR(50) )

AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @SALES_ORDER_ID	INT
	DECLARE @CUSTOMER_SHIP_ID INT
	DECLARE @COOL_LINEITEM_ID INT
	DECLARE @BILLING_ADDRESS_ID INT
	DECLARE @SHIPPING_ADDRESS_ID INT
	DECLARE @FROM_ADDRESS_ID  INT
	DECLARE @CUST_SHIP_LINE_ITEM_ID INT
	DECLARE @STATUS	VARCHAR(20)
	DECLARE @RETURN_VALUE INT
	DECLARE @CUSTOMER_NO VARCHAR(50)


	SET @SALES_ORDER_ID = NULL
	SET @FROM_ADDRESS_ID = NULL
	SET @CUSTOMER_SHIP_ID = NULL
	SET @CUST_SHIP_LINE_ITEM_ID = NULL

	SELECT @SALES_ORDER_ID = SALESORDER_ID, @BILLING_ADDRESS_ID = SO_BILLING_ADDRESS_ID,
			@SHIPPING_ADDRESS_ID = SO_SHIPPING_ADDRESS_ID FROM SALES_ORDER WHERE SALESORDER_NO = RTRIM(LTRIM(@ORDER_NO))
	SELECT @FROM_ADDRESS_ID = ADDRESS_ID FROM LOCATION WHERE L_TYPE = 'FINISHED GOODS' AND L_VENDOR_NAME = @SHIP_FROM_LOCATION
	IF @FROM_ADDRESS_ID IS NULL
	BEGIN				
			SELECT @FROM_ADDRESS_ID = ADDRESS_ID FROM CLIENT_TABLE
	END

	IF( @SALES_ORDER_ID IS NULL OR @SALES_ORDER_ID < 0 )
	BEGIN
		SELECT @CUSTOMER_NO = C_NUMBER FROM CUSTOMER WHERE C_NAME = @CUSTOMER_NAME
		IF @CUSTOMER_NO IS NULL OR LEN(LTRIM(RTRIM(@CUSTOMER_NO))) = 0
		BEGIN
			SET @CUSTOMER_NO = @CUSTOMER_NAME
		END

		EXEC SP_CREATE_UPDATE_SALES_ORDER_LINE_ITEM @ORDER_NO, @CUSTOMER_NO, @CUSTOMER_PO, '', '', @LINE_NO, @SHIPMENT_NO, @PART_NUMBER, @LINE_ITEM_SHIP_QTY,
		@UNIT_PRICE, 	@REQUESTED_DATE, @SCH_DATE, @BOOKING_DATE, '', @SHIP_FROM_LOCATION, '', '', @SHIPPED_DATE, '', '', @REV_SCH_DATE, @RETURN_VALUE, null, @IS_SAMPLE, null, ''
		, ' ', '', '', '', '', '' ,'', @CUSTOMER_NAME, '', null, 0, @PART_NUMBER, 1 ,@SHIP_REQ_NO

		SELECT @SALES_ORDER_ID = SALESORDER_ID, @BILLING_ADDRESS_ID = SO_BILLING_ADDRESS_ID,
		@SHIPPING_ADDRESS_ID = SO_SHIPPING_ADDRESS_ID FROM SALES_ORDER WHERE SALESORDER_NO = RTRIM(LTRIM(@ORDER_NO))
		SELECT @FROM_ADDRESS_ID = ADDRESS_ID FROM LOCATION WHERE L_TYPE = 'FINISHED GOODS' AND L_VENDOR_NAME = @SHIP_FROM_LOCATION
		IF @FROM_ADDRESS_ID IS NULL
		BEGIN				
				SELECT @FROM_ADDRESS_ID = ADDRESS_ID FROM CLIENT_TABLE
		END
	END 
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT _NUMBER' WHERE LOT_NUMBER = 'LOT _NUMBER'
	END

	
	IF ( @SALES_ORDER_ID IS NOT NULL ) 
	BEGIN
		--SELECT * FROM Customer_Shipping_Table
		SELECT @CUSTOMER_SHIP_ID = CUSTOMERSHIPPING_ID FROM Customer_Shipping_Table WHERE LABEL = RTRIM(LTRIM(@LABEL_NUMBER)) AND SALESORDER_ID = @SALES_ORDER_ID
		
		IF @CUSTOMER_SHIP_ID IS NULL
		BEGIN
			INSERT INTO Customer_Shipping_Table(LABEL, SALESORDER_ID, CST_STATUS, CST_From_Address_ID, CST_To_Address_ID, CST_Ship_Date, CST_Generated_By, CST_Updated_By, CST_Updated_On, [TIMESTAMP], CST_BILLING_ADDRESS_ID    )
				VALUES(RTRIM(LTRIM(@LABEL_NUMBER)), @SALES_ORDER_ID, @LINEITEM_STATUS, @FROM_ADDRESS_ID, @SHIPPING_ADDRESS_ID, @SHIPPED_DATE, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, @BILLING_ADDRESS_ID )
			SELECT @CUSTOMER_SHIP_ID = @@IDENTITY
		END
		ELSE
		BEGIN
				UPDATE Customer_Shipping_Table SET LABEL = RTRIM(LTRIM(@LABEL_NUMBER)), CST_From_Address_ID = @FROM_ADDRESS_ID, CST_To_Address_ID = @SHIPPING_ADDRESS_ID, CST_Ship_Date= @SHIPPED_DATE, CST_Updated_On = CURRENT_TIMESTAMP WHERE CUSTOMERSHIPPING_ID = @CUSTOMER_SHIP_ID
		END
		
		--SELECT * FROM CUSTOMER_OPEN_ORDER_LINEITEM
		SELECT @COOL_LINEITEM_ID = LINEITEM_ID, @STATUS = COOL_STATUS FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALES_ORDER_ID AND COOL_LINE_ITEM_NO = @LINE_NO AND COOL_SHIPMENT_NO = @SHIPMENT_NO
		IF  @COOL_LINEITEM_ID IS NULL
		BEGIN
			EXEC SP_CREATE_UPDATE_SALES_ORDER_LINE_ITEM @ORDER_NO, @CUSTOMER_NAME, @CUSTOMER_PO, '', '', @LINE_NO, @SHIPMENT_NO, @PART_NUMBER, @LINE_ITEM_SHIP_QTY,
			@UNIT_PRICE, 	@REQUESTED_DATE, @SCH_DATE, @BOOKING_DATE, '', @SHIP_FROM_LOCATION, '', '', @SHIPPED_DATE, '', '', @REV_SCH_DATE, @RETURN_VALUE, null, 1, null, ''
			, ' ', '', '', '', '', '' ,'', @CUSTOMER_NAME, '', null, 0, @PART_NUMBER ,1,@SHIP_REQ_NO

			SELECT @SALES_ORDER_ID = SALESORDER_ID, @BILLING_ADDRESS_ID = SO_BILLING_ADDRESS_ID,
			@SHIPPING_ADDRESS_ID = SO_SHIPPING_ADDRESS_ID FROM SALES_ORDER WHERE SALESORDER_NO = RTRIM(LTRIM(@ORDER_NO))
			SELECT @FROM_ADDRESS_ID = ADDRESS_ID FROM LOCATION WHERE L_TYPE = 'FINISHED GOODS' AND L_VENDOR_NAME = @SHIP_FROM_LOCATION
			IF @FROM_ADDRESS_ID IS NULL
			BEGIN				
					SELECT @FROM_ADDRESS_ID = ADDRESS_ID FROM CLIENT_TABLE
			END
			SELECT @COOL_LINEITEM_ID = LINEITEM_ID, @STATUS = COOL_STATUS FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALES_ORDER_ID AND COOL_LINE_ITEM_NO = @LINE_NO AND COOL_SHIPMENT_NO = @SHIPMENT_NO

		END
		--SELECT * FROM Customer_Shipping_Line_Item_Table
		IF ( @COOL_LINEITEM_ID IS NOT NULL )
		BEGIN
			IF @STATUS <> 'SHIPPED'
			BEGIN
				UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET COOL_STATUS = 'SHIPPED' WHERE LINEITEM_ID = @COOL_LINEITEM_ID
			END
			SELECT @CUST_SHIP_LINE_ITEM_ID = CSLIT_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE CUSTOMERSHIPPING_ID = @CUSTOMER_SHIP_ID AND LINEITEM_ID = @COOL_LINEITEM_ID
			
			IF @CUST_SHIP_LINE_ITEM_ID  IS NULL
			BEGIN
				INSERT INTO CUSTOMER_SHIPPING_LINE_ITEM_TABLE ( CUSTOMERSHIPPING_ID, LINEITEM_ID, CSLIT_QTY_SHIPPED, CSLIT_SHIPPED_DATE, CSLIT_STATUS )
					VALUES ( @CUSTOMER_SHIP_ID, @COOL_LINEITEM_ID, @LINE_ITEM_SHIP_QTY, @SHIPPED_DATE, @LINEITEM_STATUS )
				SELECT @CUST_SHIP_LINE_ITEM_ID = @@IDENTITY
			END
			ELSE
			BEGIN
				 UPDATE CUSTOMER_SHIPPING_LINE_ITEM_TABLE SET CSLIT_QTY_SHIPPED = @LINE_ITEM_SHIP_QTY, CSLIT_SHIPPED_DATE = @SHIPPED_DATE , CSLIT_STATUS = @LINEITEM_STATUS WHERE CSLIT_ID = @CUST_SHIP_LINE_ITEM_ID
			END
		END
		ELSE
		BEGIN
		  ---INSERT INTO DEBUG_TABLE   ( comments, S_NO, L_NO, SH_NO ) VALUES( 'NO LINE ITEM' ,@ORDER_NO, @LINE_NO, @SHIPMENT_NO)
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT _NUMBER' WHERE LOT_NUMBER = 'LOT _NUMBER'
		END
	END
	ELSE
	BEGIN
		  --INSERT INTO DEBUG_TABLE  ( comments, S_NO ) VALUES ( 'NO SALE ORDER', @ORDER_NO )
		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT _NUMBER' WHERE LOT_NUMBER = 'LOT _NUMBER'
	END
END